package sakila.dal.commondao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import sakila.sakilaexcetion.SakilaException;

/**
 * The Class DALAccess, access database.
 */
public final class DataAccessHelper
{

	/** The data connection. */
	private static Connection conn = null;

	/**
	 * Close the database connection.
	 * 
	 * @throws SakilaException the sakila exception
	 */
	public static void closeConnection() throws SakilaException
	{
		try
		{
			DataAccessHelper.conn.close();
		}
		catch (SQLException e)
		{
			SakilaException.handleException("Cannot close the database connection", e);
		}
	}

	/**
	 * Execute query non parameter.
	 * 
	 * @param sqlQuery the string query in sql format
	 * @return the result set, result of execute query
	 * @throws SakilaException the sakila exception
	 */
	public static ResultSet executeQueryNonParameters(final String sqlQuery) throws SakilaException
	{
		Statement stat;
		ResultSet rs = null;
		try
		{
			stat = DataAccessHelper.getConnection().createStatement();
			rs = stat.executeQuery(sqlQuery);
		}
		catch (SQLException e)
		{
			SakilaException.handleException(
			        "Fails to excute sql qery non parameters : Query string : " + sqlQuery, e);
		}

		if (rs == null)
		{
			SakilaException.handleException("Result set is null, exucte query " + sqlQuery, null);
		}
		return rs;
	}

	/**
	 * Execute query with parameters.
	 * 
	 * @param sqlQuery the string query
	 * @param parameters the array of parameters
	 * @return the result set, result of execute query
	 * @throws SakilaException the sakila exception
	 */
	public static ResultSet executeQueryWithParameters(final String sqlQuery,
	        final Object[] parameters) throws SakilaException
	{
		PreparedStatement stat;
		ResultSet rs = null;
		try
		{
			stat = DataAccessHelper.getConnection().prepareStatement(sqlQuery);
			for (int i = 1; i <= parameters.length; i++)
			{
				stat.setObject(i, parameters[i - 1]);
			}
			rs = stat.executeQuery();
		}
		catch (SQLException e)
		{
			SakilaException.handleException(
			        "Fails to excute sql qery with parameters, Query string : " + sqlQuery, e);
		}

		if (rs == null)
		{
			SakilaException.handleException("Result set is null, exucte query " + sqlQuery, null);
		}
		return rs;
	}

	/**
	 * Gets the data connection.
	 * 
	 * @return the connection
	 * @throws SakilaException
	 */
	public static Connection getConnection() throws SakilaException
	{
		if (DataAccessHelper.conn == null)
		{
			try
			{
				Class.forName(DALConstant.getValue(DALConstant.JDBC_DRIVER));
				DataAccessHelper.conn = DriverManager.getConnection(
				        DALConstant.getValue(DALConstant.CONNECTION_STRING),
				        DALConstant.getValue(DALConstant.DATA_USER),
				        DALConstant.getValue(DALConstant.DATA_PASSWORD));
			}
			catch (Exception e)
			{
				String errMsg = "";
				if (e instanceof SQLException)
				{
					errMsg = "Fails to connect database";
					SakilaException.handleException(errMsg, e);
				}
				else
					if (e instanceof ClassNotFoundException)
					{
						errMsg = "Fails to identify JDBC driver";
						SakilaException.handleException(errMsg, e);
					}
					else
					{
						errMsg = "Error when get data connection";
						SakilaException.handleException(errMsg, e);
					}
			}
		}
		return DataAccessHelper.conn;
	}
}
